-- Run this as sys
CREATE TABLESPACE biblio_tbs 
   DATAFILE '/usr/lib/oracle/xe/oradata/XE/bibilio.dbf' 
   SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

CREATE TEMPORARY TABLESPACE biblio_tmp_tbs
   TEMPFILE '/usr/lib/oracle/xe/oradata/XE/bibilio_tmp.dbf' 
   SIZE 3M
   AUTOEXTEND ON NEXT 500K MAXSIZE 50M;

CREATE USER biblio
   IDENTIFIED BY b1bl10 
   DEFAULT TABLESPACE biblio_tbs
   TEMPORARY TABLESPACE biblio_tmp_tbs;


ALTER USER biblio 
   DEFAULT TABLESPACE biblio_tbs
   TEMPORARY TABLESPACE biblio_tmp_tbs
   ACCOUNT UNLOCK;

-- GRANTS AND ROLES
GRANT CONNECT, RESOURCE TO biblio;
ALTER USER biblio DEFAULT ROLE RESOURCE, CONNECT;

------------------------------------------------
------------------------------------------------
-- connetcing to the new created schema
conn biblio@localhost/b1bl10;


-- droping tables

DROP TABLE hire;
DROP TABLE reader;
DROP TABLE author_book;
DROP TABLE author;
DROP TABLE book;
DROP TABLE category;
DROP TABLE log_tab;
------------------------

-- creating tables

CREATE TABLE reader(
   reader_id int,
   fname varchar(50 char),
   lname varchar(50 char),
   zip_code varchar(50 char),
   city varchar(50 char),
   address varchar(100 char),
   email varchar(100 char),
   CONSTRAINT reader_pk PRIMARY KEY(reader_id)
);

CREATE TABLE category(
   category_id int,
   name varchar(60 char),
   parent_category_id int,
   CONSTRAINT category_pk PRIMARY KEY(category_id)
);

CREATE TABLE author_book(
   author_id int,
   book_id int,
   CONSTRAINT author_book_pk PRIMARY KEY(book_id, author_id)
);

CREATE TABLE book(
   book_id int,
   title varchar(300 char),
   pages_num int,
   isbn varchar(50 char),
   publishing_date date,
   category_id int NOT NULL,
   CONSTRAINT book_pk PRIMARY KEY(book_id)
);

CREATE TABLE author(
   author_id int,
   fname varchar(50 char),
   lname varchar(50 char),
   CONSTRAINT author_pk PRIMARY KEY(author_id)
);

CREATE TABLE hire(
   hire_id int,
   hire_date timestamp NULL,
   return_date timestamp NULL,
   reader_id int NULL,
   book_id int NULL,
   CONSTRAINT hire_pk PRIMARY KEY (hire_id)
);	  

CREATE TABLE log_tab (
  log_tab_id number,
  proc_name varchar2(100 byte),
  error_prior varchar2(5) CONSTRAINT err_prior_chk CHECK (error_prior IN ('FATAL','ERROR','WARN', 'INFO', 'DEBUG')),
  date_of_call timestamp(4) DEFAULT SYSDATE,
  error_num number,
  params varchar2(4000 byte),
  error_desc varchar2(4000 byte),
  stack varchar2(4000 byte), 
  CONSTRAINT log_tab_pk PRIMARY KEY (log_tab_id)
);
----------------------------------------------------------------------

-- constraints
  
ALTER TABLE hire ADD CONSTRAINT hire_reader_fk FOREIGN KEY(reader_id)
REFERENCES reader (reader_id); 
 
ALTER TABLE hire ADD CONSTRAINT hire_book_fk FOREIGN KEY(book_id)
REFERENCES book (book_id);

ALTER TABLE book ADD CONSTRAINT book_category_fk FOREIGN KEY(category_id)
REFERENCES category (category_id);	  
	  
ALTER TABLE author_book ADD CONSTRAINT book_author_fk FOREIGN KEY(book_id)
REFERENCES book (book_id); 
 
ALTER TABLE author_book ADD CONSTRAINT author_book_fk FOREIGN KEY(author_id)
REFERENCES author (author_id); 
-------------------------------------------------------------------------

-- indexes
CREATE INDEX log_tab_date_of_call_idx ON log_tab(date_of_call);
-------------------------------------------------------------------------

-- sequences
CREATE SEQUENCE reader_seq
 START WITH 1
 INCREMENT BY 1;

CREATE SEQUENCE category_seq
 START WITH 1
 INCREMENT BY 1;

CREATE SEQUENCE book_seq
 START WITH 1
 INCREMENT BY 1;

CREATE SEQUENCE author_seq
 START WITH 1
 INCREMENT BY 1;

CREATE SEQUENCE hire_seq
 START WITH 1
 INCREMENT BY 1;
 
CREATE SEQUENCE log_tab_seq
 START WITH 1
 INCREMENT BY 1; 

--------------------------------------------------------------
-- extension 
CREATE TABLE punishment (
  punishment_id number,
  val number(6,2),
  msg varchar2(300 char),
  reader_id int NULL,
  book_id int NULL,
  CONSTRAINT punishment_pk PRIMARY KEY (punishment_id)
);

----------------------------------------------------------------------

-- constraints
  
ALTER TABLE punishment ADD CONSTRAINT punishment_reader_fk FOREIGN KEY(reader_id)
REFERENCES reader (reader_id); 
 
ALTER TABLE punishment ADD CONSTRAINT punishment_book_fk FOREIGN KEY(book_id)
REFERENCES book (book_id);
 
-------------------------------------------------------------------------
-- sequences
CREATE SEQUENCE punishment_seq
 START WITH 1
 INCREMENT BY 1;









